/*
 * @Author: tcosfish
 * @Date: 2022-07-20 17:21:46
 * @LastEditors: tcosfish
 * @LastEditTime: 2022-08-18 11:41:29
 * @FilePath: \loadFile\src\utils\transXLSForXLSX.util.ts
 */

import * as xlsx from "xlsx"
import * as ExcelJs from "exceljs"

export async function transXLS(
  filepath: string,
  file_format: "xlsx" | "csv" = "xlsx"
) {
  const workbook = xlsx.readFile(filepath)
  const workbook_save: xlsx.WorkBook = {
    Sheets: {},
    SheetNames: [],
  }

  // 生成对应的xlsx文件路径
  let temp = filepath.split("\\")
  let file_name = temp.pop()
  temp.pop()
  file_name = file_name?.substring(0, file_name.lastIndexOf("."))
  let file_path = `${temp.join(
    "\\"
  )}\\${file_format}\\${file_name}.${file_format}`

  // 转化为 XLSX 文件
  for (const sheet of workbook.SheetNames) {
    const header: string[] = [
      "code",
      "name",
      "x1",
      "x2",
      "x3",
      "debit_amount",
      "credit_amount",
      "x4",
      "x5",
      "debit_closing_balance",
      "credit_closing_balance",
      "id",
    ]
    const objects = xlsx.utils.sheet_to_json(workbook.Sheets[sheet], {
      header: header,
      skipHidden: true,
    })
    // console.dir(objects)
    objects.forEach((obj: any) => {
      console.log(obj["debit_closing_balance"] ?? 0)
    })
    workbook_save.SheetNames.push(sheet)
    workbook_save.Sheets[sheet] = xlsx.utils.json_to_sheet(objects)
  }
  await xlsx.writeFile(workbook_save, file_path)
  return file_path
}

export async function toXLSX(filepath: string) {
  const ext = filepath.substring(filepath.lastIndexOf(".") + 1)
  if (ext === "xls") {
    filepath = await transXLS(filepath)
  }
  return filepath
}

// 处理 xls文件函数
export async function readXLSX(filepath: string) {
  // 判断 xlsx/xls 文件
  filepath = await toXLSX(filepath)

  // 读取 xlsx 文件
  const workbook = new ExcelJs.Workbook()
  await workbook.xlsx.readFile(filepath)
  workbook.eachSheet((worksheet) => {
    worksheet.eachRow((row) => {
      console.dir(row.values)
    })
  })
  console.log("Good")
}
